﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;

namespace Sys.Data
{
    /// <summary>
    /// SQLHelper
    /// 数据库操作，用于读取BPAMS系统的数据库数据（跨库读取）
    /// Luo.Z.X 20150825
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public static SqlConnection GetSqlConnection()
        {
            SqlConnection conn = null;
            string connectionString = ConfigurationManager.ConnectionStrings["BpamsEntities"].ConnectionString;  //连接字符串从Web.config中取
            //string connectionString = ConfigurationManager.ConnectionStrings["Entities"].ConnectionString;
            //string connectionString = ConfigurationManager.ConnectionStrings[0].ConnectionString;
            //throw new Exception(connectionString);
            //string connectionString = @"Data Source=127.0.0.1;Initial Catalog=TestDB;User Id=sa;Password=123458;";  //测试专用
            if (!String.IsNullOrEmpty(connectionString))
            {
                conn = new SqlConnection(connectionString);
            }
            return conn;
        }

        /// <summary>
        /// 返回执行结果的第一行第一列
        /// </summary>
        /// <param name="sqlString"></param>
        /// <returns></returns>
        public static object GetSqlScalar(string sqlString)
        {
            object result = null;
            if (!String.IsNullOrEmpty(sqlString))
            {
                var conn = GetSqlConnection();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlString, conn);
                    result = cmd.ExecuteScalar();
                }
            }
            return result;
        }

        /// <summary>
        /// 获取DataReader
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <returns></returns>
        public static SqlDataReader GetSqlDataReader(string sqlString)
        {
            SqlDataReader sdr = null;
            if (!String.IsNullOrEmpty(sqlString))
            {
                var conn = GetSqlConnection();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlCommand cmd = new SqlCommand(sqlString, conn);
                    sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            return sdr;
        }

        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <param name="sqlString">sql语句</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static DataSet GetSqlDataSet(string sqlString, string tableName)
        {
            DataSet ds = null;
            using (var conn = GetSqlConnection())
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    SqlDataAdapter adt = new SqlDataAdapter(sqlString, conn);
                    ds = new DataSet();
                    adt.Fill(ds, tableName);
                }
            }
            return ds;
        }
    }
}
